/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;
import DTO.AcademicDTO;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author QuangMinh
 */
public class AcademicDAO {
    private ResultSet rs = null;
    private PreparedStatement pstDetails = null;
    private Connection con = null;

    public void addAcademic(AcademicDTO Academic){
    try{
        con = DataConnection.getConnection();
        pstDetails = con.prepareStatement("INSERT INTO ACADEMIC VALUES(?,?,?,?,?)");
        pstDetails.setInt(1, Academic.getStudentID());
        pstDetails.setString(2,Academic.getSubID());
        pstDetails.setString(3,Academic.getBatchName());
        pstDetails.setString(4,Academic.getStartDate());
        pstDetails.setString(5,Academic.getEndDate());
        pstDetails.executeUpdate();
        pstDetails.close();
        con.close();
    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, "Cannot add this entry");
    }
}

    public ArrayList<AcademicDTO> getAca(){
        ArrayList<AcademicDTO> list = new ArrayList<AcademicDTO>();
        try{
            con = DataConnection.getConnection();
            pstDetails = con.prepareStatement("SELECT * FROM ACADEMIC", ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = pstDetails.executeQuery();
            rs.beforeFirst();
            while(rs.next())
            {
                AcademicDTO academicDTO = new AcademicDTO();
                academicDTO.setStudentID(rs.getInt("StudentID"));
                academicDTO.setSubID(rs.getString("SubID"));
                academicDTO.setBatchName(rs.getString("BatchName"));
                academicDTO.setStartDate(rs.getString("StartDate"));
                academicDTO.setEndDate(rs.getString("EndDate"));
                list.add(academicDTO);
            }
            rs.close();
            pstDetails.close();
            con.close();
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return list;
    }
    public ArrayList<AcademicDTO> getAcaByStuID(int stuID){
        ArrayList<AcademicDTO> list = new ArrayList<AcademicDTO>();
        try{
            con = DataConnection.getConnection();
            pstDetails = con.prepareStatement("SELECT * FROM ACADEMIC WHERE STUDENTID=?", ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            pstDetails.setInt(1, stuID);
            rs = pstDetails.executeQuery();
            while(rs.next())
            {
                AcademicDTO academicDTO = new AcademicDTO();
                academicDTO.setStudentID(rs.getInt("StudentID"));
                academicDTO.setSubID(rs.getString("SubID"));
                academicDTO.setBatchName(rs.getString("BatchName"));
                academicDTO.setStartDate(rs.getString("StartDate"));
                academicDTO.setEndDate(rs.getString("EndDate"));
                list.add(academicDTO);
            }
            rs.close();
            pstDetails.close();
            con.close();
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return list;
    }

        public AcademicDTO getAcaByStuSubID(int stuID, String subID){
        AcademicDTO academicDTO = new AcademicDTO();
        try{
            con = DataConnection.getConnection();
            pstDetails = con.prepareStatement("SELECT * FROM ACADEMIC WHERE STUDENTID = ? AND SUBID = ?", ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            pstDetails.setInt(1, stuID);
            pstDetails.setString(2, subID);
            rs = pstDetails.executeQuery();
            rs.beforeFirst();
            if(rs.next())
            {
                academicDTO.setStudentID(rs.getInt("StudentID"));
                academicDTO.setSubID(rs.getString("SubID"));
                academicDTO.setBatchName(rs.getString("BatchName"));
                academicDTO.setStartDate(rs.getString("StartDate"));
                academicDTO.setEndDate(rs.getString("EndDate"));
            }
            else return null;
            rs.close();
            pstDetails.close();
            con.close();
        }catch(Exception ex){
            return null;
        }
        return academicDTO;
    }

    public  void delAcademic(int StuID, String subID){
    try{
    con = DataConnection.getConnection();
    pstDetails = con.prepareStatement("DELETE FROM ACADEMIC WHERE STUDENTID =? AND SUBID = ?");
    pstDetails.setInt(1,StuID);
    pstDetails.setString(2,subID);
    pstDetails.executeUpdate();
    pstDetails.close();
    con.close();
    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, "Cannot delete this entry");
    }
    }

    public  void editAcademic(String batchName, String startDate, String endDate, int studentID, String subID){
    try{
        con = DataConnection.getConnection();
        pstDetails = con.prepareStatement("UPDATE ACADEMIC SET BATCHNAME = ?, STARTDATE= ?, ENDDATE= ? WHERE STUDENTID =? AND SUBID = ?", ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        pstDetails.setString(1,batchName);
        pstDetails.setString(2,startDate);
        pstDetails.setString(3,endDate);
        pstDetails.setInt(4,studentID);
        pstDetails.setString(5, subID);
        pstDetails.executeUpdate();
        pstDetails.close();
        con.close();
    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, "Cannot update this entry");

    }
    }

}
